﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_IMG001:DA_Matrix
    {
        DA_MISParameter para = null;
        DA_MISParameter PARA
        {
            get
            {
                if (para == null)
                {
                    para = new DA_MISParameter();
                }
                return para;
            }        
        }

        public DA_IMG001()
        {
            tableName = "IM001";
        }

        public override string[] GetDistinctField()
        {
            return new string[] { "項目" };
        }

        public override DataSet GetCriteria()
        {
            tableName = "IMG001_item";
            DataSet ds =base.GetCriteria();
            tableName = "IM001";
            return ds;
        }


        public DataTable GetIM001Accno(string p_accno)
        {
            p_accno = string.Format("{0}%", p_accno.ToUpper().Trim());
            string sql = @"
SELECT TOP 10 科目代號	
FROM(
	SELECT DISTINCT 科目代號
	FROM IM001
)A WHERE 科目代號 LIKE @p_accno
";
            FillTableSchema("IM001");
            FillFieldList(new string[] { p_accno }
                , new string[] { "@p_accno" }, "科目代號");
            AddParameter(sql);
            return getData();
        }

        public bool Insert(string p_item, string p_accno)
        {
            DataTable dt = GetPartialData(p_item, p_accno);
            if (!dt.Rows.Count.Equals(0))
            {
                return false;
            }
            string sql = @"
INSERT INTO MISParameter VALUES
(@fldname,@fldvalue,@team,(SELECT COUNT(*)+100  FROM MISParameter WHERE team = 'IMG001' AND para1 = 'IMG001')
,@para1)
";
            string[] fields = {"fldname","fldvalue","team","para1"};
            string[] values = {p_item,p_accno,"IMG001","IMG001"};
            string[] paras = { "@fldname", "@fldvalue", "@team","@para1" };

            return PARA.ExecuteByOutSiteStatement(fields, values, paras, sql);
        }

        public bool Del(string p_item, string p_accno,string p_seq)
        {
            string sql = @"
DELETE FROM MISPARAMETER WHERE fldvalue = @fldvalue
AND fldname = @fldname
AND seq = @seq
AND para1='IMG001'
";

            string[] fields = { "fldvalue", "fldname","seq" };
            string[] values = { p_accno ,p_item,p_seq };
            string[] paras = { "@fldvalue", "@fldname","@seq" };

            return PARA.ExecuteByOutSiteStatement(fields, values, paras, sql);
        }


        /// <summary>
        /// 取回部份資料或單一資料
        /// </summary>
        /// <param name="p_item"></param>
        /// <param name="p_accno"></param>
        /// <returns></returns>
        public DataTable GetPartialData(string p_item, string p_accno)
        {
            string sql;
            StringBuilder sb = new StringBuilder();
            List<string> fields = new List<string>();
            List<string> values = new List<string>();
            List<string> paras = new List<string>();
            if (!string.IsNullOrEmpty(p_accno))
            {
                sb.AppendFormat(" AND fldvalue = @fldvalue");
                fields.Add("fldvalue");
                values.Add(p_accno);
                paras.Add("@fldvalue");
            }
            if (!string.IsNullOrEmpty(p_item))
            {
                sb.AppendFormat(" AND fldname = @fldname");
                fields.Add("fldname");
                values.Add(p_item);
                paras.Add("@fldname");

            }
            sql = string.Format(
                "SELECT * FROM MISPARAMETER WHERE para1='IMG001' {0}"
                , sb.ToString());
            //FillFieldList(values.ToArray<string>(), paras.ToArray<string>()
            //    , fields.ToArray<string>());
            //AddParameter(sql);
            return PARA.GetParameterBySQL(fields.ToArray<string>()
                , values.ToArray<string>(), paras.ToArray<string>(), sql);
        }

        public DataTable GetSummaryData(string[] p_twYMs)
        {
            GetCriteriaString();
            CriteriaString = CriteriaString.Replace("AND 項目", "AND A.項目");
            string sql, v0, v1,v2;
            v2 = string.Format("AND YYYYMM BETWEEN '{0}' AND '{1}'"
                , p_twYMs[0],p_twYMs[11]);
            v0 = Utility.GetYearMonthString(p_twYMs
                , ",MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.BAL END) BAL_{0}"
                + System.Environment.NewLine);
            v1 = Utility.GetYearMonthString(p_twYMs
                , ",BAL_{0}" + System.Environment.NewLine);
            sql = string.Format(getSQLStatement()
                , v0, v1, CriteriaString, getDiffString(p_twYMs[0].Substring(0,3)), v2);
            AddParameter(sql);
            return getData();
        }

        public DataTable GetDetailData(Dictionary<string, string> p_dict)
        {
            StringBuilder sb = new StringBuilder();
            string criteriaStr = "AND {0} = '{1}'";
            foreach (KeyValuePair<string, string> key in p_dict)
            {
                sb.AppendFormat(criteriaStr, key.Key.ToString()
                    , key.Value.ToString());
            }

            string sql = string.Format(getDetailSQLStatement(), sb.ToString());
            return getData(sql, "detail");
        }

        protected string getSQLStatement()
        {
            return @"
SELECT 
    A.item
    {1}
    {3}
	,B.BAL
	,B.verify	
FROM
(
	SELECT
		A.item
		,A.NEWNO
		{0}
	FROM(
		SELECT 
			ISNULL(A.item,'SUBTOTAL')ITEM
			,A.NEWNO
			,A.YYYYMM
			,A.BAL 
		FROM(
			SELECT
				A.item
				,A.NEWNO
				,A.YYYYMM	
				,GROUPING_ID(A.YYYYMM,A.NEWNO,A.item) GRPID
				,CONVERT(NVARCHAR,CAST(SUM(A.餘額) AS MONEY),1) BAL
			FROM(
			SELECT 
				A.item
				,CASE 
					WHEN LEFT(A.科目代號,4) = '4903' THEN '4903'
					WHEN LEFT(A.科目代號,4) = '4902' THEN '4902'
					WHEN LEFT(A.科目代號,4) IN ('4541','4542') THEN '4541_4542'
					WHEN LEFT(A.科目代號,4) IN ('4568','4569') THEN '4568_4569'
				END NEWNO
				,B.YYYYMM
				,B.餘額
			FROM IMG001_ITEM A LEFT OUTER JOIN IM001 B
			ON A.科目代號 = B.科目代號
			WHERE 1=1
            {2}
            {4}
			)A
			GROUP BY CUBE(A.YYYYMM,A.NEWNO,A.item)
		)A WHERE A.GRPID IN (0,1)
												
		UNION ALL
	
	
		SELECT 
			'TOTAL' AS ITEM
			,'' NEWNO
			,B.YYYYMM
			,CONVERT(NVARCHAR,CAST(SUM(B.餘額) AS MONEY),1) BAL
		FROM IMG001_ITEM A LEFT OUTER JOIN IM001 B
		ON A.科目代號 = B.科目代號
		WHERE 1=1
        {2}
        {4}
		GROUP BY B.YYYYMM   
		
	)A GROUP BY A.ITEM,A.NEWNO
)A LEFT OUTER JOIN 
(--
SELECT
	A.ITEM
	,A.NEWNO
	,A.BAL
	,B.verify
FROM
(
	SELECT
		CASE WHEN A.GRPID = 3 THEN 'TOTAL' 
			ELSE ISNULL(A.item,'SUBTOTAL')
		END ITEM
		,CASE WHEN A.GRPID = 3 AND A.AA IS NULL THEN ''
			ELSE  A.AA END NEWNO
		,A.bal
	FROM(
		SELECT
			A.item
			,A.AA
			,CONVERT(NVARCHAR,CAST(SUM(A.bal) AS MONEY),1) bal
			,GROUPING_ID(A.ITEM,A.AA) GRPID
		FROM
		(
				SELECT
					A.item
					,CASE 
							WHEN LEFT(A.科目代號,4) = '4903' THEN '4903'
							WHEN LEFT(A.科目代號,4) = '4902' THEN '4902'
							WHEN LEFT(A.科目代號,4) IN ('4541','4542') THEN '4541_4542'
							WHEN LEFT(A.科目代號,4) IN ('4568','4569') THEN '4568_4569'
						 END AA
					,B.餘額 bal
				FROM IMG001_ITEM A LEFT OUTER JOIN
					IM001 B 
				ON A.科目代號 = B.科目代號
                WHERE 1=1
                {2}
                {4}
		)A GROUP BY CUBE(A.ITEM,A.AA)
)A 
WHERE A.GRPID IN (2,0,3) 
)A LEFT OUTER JOIN 
(
	SELECT 
		CASE WHEN A.GRPID = 3 THEN 'TOTAL' 
			ELSE ISNULL(A.item,'SUBTOTAL')
		END ITEM		
		,CASE WHEN A.GRPID = 3 AND A.AA IS NULL THEN ''
			ELSE  A.AA END NEWNO
		,A.verify
	FROM
	(
		SELECT
			A.item
			,A.AA
			,CONVERT(NVARCHAR,CAST(SUM(A.verify) AS MONEY),1) verify
			,GROUPING_ID(A.ITEM,A.AA) GRPID
		FROM
		(
				SELECT
					A.item
					,MAX(CASE 
							WHEN LEFT(A.科目代號,4) = '4903' THEN '4903'
							WHEN LEFT(A.科目代號,4) = '4902' THEN '4902'
							WHEN LEFT(A.科目代號,4) IN ('4541','4542') THEN '4541_4542'
							WHEN LEFT(A.科目代號,4) IN ('4568','4569') THEN '4568_4569'
						 END) AA
					,SUM(B.verify)verify
				FROM IMG001_ITEM A LEFT OUTER JOIN
					(
						SELECT * 
						FROM IM001 B 
						WHERE B.YYYYMM = (SELECT MAX(A.YYYYMM) MAX_YM FROM IM001 A
                                        WHERE 1=1
                                         {4}
                                        )      
					)B 
				ON A.科目代號 = B.科目代號
                WHERE 1=1
                {2}
				GROUP BY A.item
		)A
		GROUP BY CUBE(A.ITEM,A.AA)
	)A WHERE GRPID IN (0,2,3)
)B ON A.NEWNO = B.NEWNO AND A.ITEM = B.ITEM
)B ON A.NEWNO = B.NEWNO AND A.ITEM = B.ITEM
ORDER BY A.NEWNO,A.ITEM
";
        }

        protected string getDetailSQLStatement()
        {
            return @"
SELECT * FROM IM001 B
WHERE B.科目代號 IN (SELECT 科目代號 FROM IMG001_ITEM A
WHERE 1=1 {0})
";
        }

        /// <summary>
        /// 取回前面兩個月，要用最近的一個月-去前一個月
        /// </summary>
        /// <returns></returns>
        protected string getDiffString(string p_twYYY)
        {
            string sql,diff;
            sql = string.Format(@"
SELECT YYYYMM FROM (
    SELECT TOP 2 A.YYYYMM 
    ,ROW_NUMBER() OVER(ORDER BY YYYYMM DESC)AA
FROM
(
	SELECT DISTINCT YYYYMM
	FROM IM001 A
    WHERE A.YYYYMM LIKE '{0}%'	
)A 
ORDER BY A.YYYYMM DESC)A 
",p_twYYY);
            DataTable mm = getData(sql, "mon");
            string[] months = {mm.Rows[0][0].ToString()
                                  ,mm.Rows[1][0].ToString() };
            diff = string.Format(
                ",CONVERT(NVARCHAR,CAST(CAST(BAL_{0} AS MONEY) -CAST(BAL_{1} AS MONEY) AS MONEY),1) AS DIFF"
                ,mm.Rows[0][0].ToString()
                ,mm.Rows[1][0].ToString());
            return diff;
        }

    }
}
